PostgreSQLをWindows環境からLinux環境に移行する際に注意したい照合順序の問題

PostgreSQL on Linuxの環境ではlc_xxx系のパラメータにJapanese_Japan.932が指定できないので注意しましょう！！

#データベース

#Amazon RDS

#PostgreSQL

#AWS

岩田智哉

2019.09.06

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

CX事業本部@大阪の岩田です。

ライセンスコストの削減等を目的にPostgreSQL on Windows Serverの環境からEC2やRDSの環境に移行するようなユースケースではPostgreSQLのlc_xxx系のパラメータに注意が必要です。日本語環境のWindowsに「次へ」、「次へ」で標準インストールしたPostgreSQLはlc_xxx系のパラメータがJapanese_Japan.932でデータベースクラスタが構築されます。このJapanese_Japan.932というパラメータはWindows上にPostgreSQLをインストールした場合しか指定できないため、Linux環境ではlc_xxx系のパラメータに何を設定するべきか検討する必要があります。

このブログではlc_collateの指定によるソート順の違いと、ICUサポートを利用した場合のソート順についてご紹介します。

環境

今回検証に利用した環境です。

Windows環境
- Windows_Server-2016-Japanese-Full-Base-2019.08.16 (ami-09c688c3fdee6f78f)
- PostgreSQL 11.5, compiled by Visual C++ build 1914, 64-bit
RDS環境
- PostgreSQL 11.4-R1

PostgreSQLへの接続とSQLの発行はpsqlを利用しました。クライアント側のエンコーディングはUTF-8を、データベース側のエンコーディングも全てUTF-8を利用しています。

PostgreSQL on Windowsの場合

まずWindows環境で試してみます。

CREATE DATABASE cp932;
CREATE TABLE t (t text);

テーブルを作成し、テストデータを投入します。投入用のSQLはこちらのブログから拝借しました。

PostgreSQL 10のICUコレーションを使うと日本語を普通にソートでき、更に文字順序までカスタマイズできる

INSERT INTO t (t) VALUES 
('う'), ('ｳ'), ('C'), ('かさ'), ('３'), ('c'), ('ｲ'), ('がく'), ('Ｃ'), ('ウ'),  
('1'), ('ｱ'), ('b'), ('2'), ('Ｂ'), ('B'), ('１'), ('Ａ'),('い'),('ア'), 
('か'), ('A'), ('a'), ('AbC'), ('aBc');

ソートしてみます

cp932=> select * from t order by t ;
  t
------
 1
 １
 2
 ３
 a
 A
 Ａ
 aBc
 AbC
 b
 B
 Ｂ
 c
 C
 Ｃ
 ｱ
 ア
 ｲ
 い
 ｳ
 ウ
 う
 か
 がく
 かさ
(25 rows)

RDS環境の場合

続いてRDS環境でも試してみます。日本語の環境ではlc_collateにはCもしくはja_JP.UTF-8を指定するケースが多いと思うので、この2パターンとICUサポートを利用するパターンの合計3パターンで試してみます。テーブルの作成やテストデータの投入はWindows環境と同様の手順となります。

なお、RDS環境でlc_collateを指定する方法はこちらのブログをご参照ください。

RDS for PostgreSQLのロケールをCにする

lc_collate=Cの場合

lc_collateがC(no-localeも同様)のDBを作って、Windows環境と同様のSQLを実行してみます。

no_locale=> select * from t order by t ;
  t
------
 1
 2
 A
 AbC
 B
 C
 a
 aBc
 b
 c
 い
 う
 か
 かさ
 がく
 ア
 ウ
 １
 ３
 Ａ
 Ｂ
 Ｃ
 ｱ
 ｲ
 ｳ
(25 rows)

うーん。。。

Windows環境とのdiffです

2d1
< １
4,5d2
< ３
< a
7,8d3
< Ａ
< aBc
10d4
< b
12,13d5
< Ｂ
< c
15,18c7,10
< Ｃ
< ｱ
< ア
< ｲ
---
> a
> aBc
> b
> c
20,21d11
< ｳ
< ウ
24d13
< がく
25a15,25
> がく
> ア
> ウ
> １
> ３
> Ａ
> Ｂ
> Ｃ
> ｱ
> ｲ
> ｳ

ガッツリ差分が出ています。

ja_JP.UTF-8の場合

続いてlc_collateにja_JP.UTF-8を指定した場合です。

ja_jp_utf8=> select * from t order by t;
  t
------
 1
 2
 A
 AbC
 B
 C
 a
 aBc
 b
 c
 ｱ
 ｲ
 ｳ
 １
 ３
 Ａ
 Ｂ
 Ｃ
 い
 う
 か
 かさ
 がく
 ア
 ウ

Windows環境とのdiffです

2d1
< １
4,5d2
< ３
< a
7,8d3
< Ａ
< aBc
10d4
< b
12,13d5
< Ｂ
< c
15c7,10
< Ｃ
---
> a
> aBc
> b
> c
17d11
< ア
19d12
< い
21c14,19
< ウ
---
> １
> ３
> Ａ
> Ｂ
> Ｃ
> い
24d21
< がく
25a23,25
> がく
> ア
> ウ

ほんの少しだけ差分が減りました。

ICUサポートを利用した場合

RDS環境ではICUサポートが利用可能です。 ICUライブラリを利用してORDER BY句の指定にja-x-icuの指定を追加してみましょう。※この場合はデータベースのlc_collateはソート順に関係ありません。

 ja_jp_utf8=> select * from t order by t COLLATE "ja-x-icu";
  t
------
 1
 １
 2
 ３
 a
 A
 Ａ
 aBc
 AbC
 b
 B
 Ｂ
 c
 C
 Ｃ
 ア
 ｱ
 い
 ｲ
 う
 ウ
 ｳ
 か
 がく
 かさ
(25 rows)

Windows環境とのdiffです

16d15
< ｱ
18c17
< ｲ
---
> ｱ
20,21c19
< ｳ
< ウ
---
> ｲ
22a21,22
> ウ
> ｳ
25c25
< かさ
---
> かさ

大分Windows環境に近くなりました！あとは半角カナが全角カナより先に来てくれれば、、、というところです。 CREATE COLLATIONを使えば独自の照合順序オブジェクトを作成できるので、頑張ればWindows環境と完全に同一な日本語ソートを実現できるのかもしれませんが、ICUのサイトを見ても半角カナと全角カナのソート順を制御するようなScriptが見つからなかったので今回はあきらめました。

International Components for Unicode